{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 第三章 基于pandas和可视化分析清洗电商平台数据"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "************************************************************\n",
    "************************************************************\n",
    "## 实验3-1 数据预处理实验讲解：\n",
    "### 一、实验目的\n",
    "了解pandas的常用函数\n",
    "了解数据清洗的基础方法\n",
    "\n",
    "### 二、实验环境\n",
    "Python3开发环境，第三方包有pandas\n",
    "\n",
    "### 三、实验原理\n",
    "本实验将利用pandas该python第三方包对爬取的数据进行清洗。pandas 是基于NumPy 的一种工具，该工具是为了解决数据分析任务而创建的。Pandas 纳入了大量库和一些标准的数据模型，提供了高效地操作大型数据集所需的工具。pandas提供了大量能使我们快速便捷地处理数据的函数和方法。你很快就会发现，它是使Python成为强大而高效的数据分析环境的重要因素之一。\n",
    "\n",
    "### 四、步骤讲解\n",
    "参考实验手册\n",
    "此小节主要讲述的是数据预处理。主要包含以下四个部分：\n",
    "\n",
    "1、去重（去除重复数据）；\n",
    "\n",
    "2、据观察，搜索结果中仍含有红酒杯，讲解红酒的书籍等其他商品数据，此部分将利用商品属性来进行判别，红酒有酒精度，特性，品类等特征，非红酒商品则没有那些属性，以此作为判断依据进行数据的删选；\n",
    "\n",
    "3、据观察，平台上红酒的销售有瓶装，礼盒装，箱装等不同包装，即需要调整价格，且容量和包装这两个属性也并不可信，有些是总数的显示，有些是见瓶身，见包装的字样，唯一较为可靠的是销售平台的标题，此处将采用自然语言处理中的词性分析解析得到红酒的数量。为简化后续同级红酒的比较，经统计，750ml的红酒占多数，此处选择删选其他容量数据，仅提取750ml的红酒相关数据；\n",
    "\n",
    "4、对无用的列或是数据较为繁杂不准确的列进行删选；"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 五、实验步鄹\n",
    "************************************************\n",
    "### 步鄹1:上传实验数据\n",
    "\n",
    "在当前目录创建data文件夹，并从“项目课件”或者“数据中心”下载项目数据（电商红酒.csv、红酒品牌.csv、微博红酒.csv）,并将下载的数据上传到data文件夹下。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "************************************************\n",
    "### 步鄹2:处理数据格式\n",
    "\n",
    "首先利用json.loads解决爬取数据中的中文编码问题，如下代码所示：\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 479,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style>.container { width:100% !important; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "loading finished\n"
     ]
    }
   ],
   "source": [
    "import csv\n",
    "import json\n",
    "import warnings\n",
    "\n",
    "warnings.simplefilter(action=\"ignore\", category=FutureWarning)\n",
    "import numpy as np  # linear algebra\n",
    "import pandas as pd \n",
    "import matplotlib.pyplot as plt\n",
    "import math\n",
    "import seaborn as sns\n",
    "from IPython.display import display, HTML, display_html\n",
    "\n",
    "display(HTML(\"<style>.container { width:100% !important; }</style>\"))\n",
    "import missingno as msno\n",
    "import scipy.stats\n",
    "from sklearn.preprocessing import LabelEncoder\n",
    "from sklearn.ensemble import RandomForestRegressor\n",
    "from sklearn.ensemble import RandomForestClassifier\n",
    "from mlxtend.regressor import StackingCVRegressor\n",
    "from lightgbm import LGBMRegressor\n",
    "from sklearn.model_selection import cross_val_score,KFold\n",
    "from sklearn.linear_model import LinearRegression, Lasso, Ridge\n",
    "from sklearn.svm import SVR\n",
    "\n",
    "print(\"loading finished\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 480,
   "metadata": {},
   "outputs": [],
   "source": [
    "csvfile = open('./data/电商红酒.csv', 'r')\n",
    "reader = csv.reader(csvfile)#读取到的数据是将每行数据当做列表返回的\n",
    "\n",
    "rows = []#用来存储解析后的没条数据\n",
    "count = 0\n",
    "for row in reader:\n",
    "    row_str = \",\".join(row)#row为list类型需转为str，该数据变为字典型字符串\n",
    "    row_dict = json.loads(row_str)\n",
    "    title = row_dict.get('name')\n",
    "    if '葡萄酒'in title or '红酒' in title:\n",
    "        newdict = {}\n",
    "        for k in row_dict:\n",
    "            if type(row_dict[k]) == str:#将键值对赋给新字典\n",
    "                newdict[k] = row_dict[k]\n",
    "            elif type(row_dict[k]) == dict:#若存在嵌套字典，将该字典中的key和value作为属性和属性值\n",
    "                newdict.update(row_dict[k])\n",
    "        rows.append(newdict)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "然后利用pandas将数据转化为DataFrame形式，并保存为csv文件，如下代码所示："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 481,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "df = pd.DataFrame(rows)#将字典型数组转为DataFrame形式\n",
    "df.to_csv(\"wine_df_shop.csv\",encoding=\"utf-8_sig\", index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "最后查看数据量和数据维度，如下代码所示："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 482,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(30985, 229)\n"
     ]
    }
   ],
   "source": [
    "print(df.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "************************************************\n",
    "## 结果分析：\n",
    "\n",
    "可见，总共有33734条数据，266维属性；查看一下保存的文件，观察数据发现有很多属性（如CT数、ISBN、面料、页数、题材等）不属于本主题“红酒”研究属性，可能是以关键词爬取数据时获取的有关红酒书籍、醒酒器、酒具等方面的数据，这些噪声数据应予以删除，后续会介绍具体讲解。\n",
    "************************************************"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "************************************************\n",
    "### 步鄹3:处理关键字\n",
    "目前关键词字段存储的是\"品牌 红酒\"（如“拉菲 红酒”，“LAFITE 红酒”），处理流程如下：\n",
    "\n",
    "1、首先去掉关键词中的“红酒”字符,只保留红酒品牌\n",
    "\n",
    "2、其次对于红酒品牌存在中英文两种表述的，统一归为形如“拉菲/LAFITE”形式\n",
    "\n",
    "代码如下所示："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 483,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['美绚' '通化/tonghua' '长城/greatwall ' '干露/concha y toro ' '拉菲/lafite'\n",
      " '山图/shantu' '凯缘春' '布多格' '拉蒙' '卡思黛乐/castel' '波尔多' '张裕/changyu ' '罗曼尼康帝庄园'\n",
      " '圣芝/suamgy' '华东/huadong' '菲利普-德-罗斯柴尔德男爵' '黄尾袋鼠/yellow tail' '托卡伊/tokaji'\n",
      " '圣丽塔' '智象/chilephant' '汉凯/henkell' '桃乐丝/torres' '酩悦/moet&amp;chandon'\n",
      " '尼德堡' '香奈/j.p.chenet ' '天鹅庄' '杰卡斯/jacob’s creek' '罗莎庄园' '芙华/la fiole'\n",
      " '名仕爱菲尔/afeir' '莫高/mogao ' '西夫拉姆/saflam' '王朝/dynasty ' '紫桐' '爱之湾/andimar '\n",
      " '梦诺' '天阶/babylonstoren' '格拉洛/gelaluo' '木桐' '木桐古堡/ch. mouton rothschild'\n",
      " '玛歌酒庄/chateau margaux' '奔富/penfolds' '雷司' '木桐嘉棣/mouton cadet'\n",
      " '吉家乐世家/e. guigal' '拉图酒庄' '雄狮酒庄/chateau leoville-las cases' '贝灵哲/beringer'\n",
      " 'wineboss' '集美' '火烈鸟' '舒特家族' '也买酒/yesmywine' '弗莱斯凯罗/cielo e terra'\n",
      " '葡乐/taller wine' '荆凃' '奥瑞安' '红魔鬼/casillero del diablo' '纷赋/wolfblass'\n",
      " '丰收' '名庄靓年' '奥斯曼/aosiman' '路易拉菲/louis lafon ' '巴黎庄园/cmp' '长白山 ' '小龙船'\n",
      " '麦洛威尔/mellowell' '威赛帝斯' '露森/dr. loosen' '通天' '尼雅/niya ' '法莱雅'\n",
      " '布朗兄弟酒庄/brown brothers' '双洋/two oceans' '傲鱼/aoyo'\n",
      " '金蝴蝶/golden butterfly face' '莱茵黑森' '香格里拉/shangri-la' '双掌' '富隆胜卡罗' '高斯达'\n",
      " '百特/beleden' '嘉伦多' '博纳旺蒂/bonaventure' '路易亚都世家/louis jadot '\n",
      " '活灵魂/almaviva' '夏迪/hardys' ' 姚明/yaoming' '菲斯奈特/freixenet' '丁戈树/dinggeshu'\n",
      " '威龙/wilon' '白洋河/baiyanghe' '蒙特斯/montes ' '巴黎之光' '翡马/bordeauxvineam' '力士金'\n",
      " '云端' '卡赛欧' '若诗庄园/rosemount' '中澳袋鼠/c&a kangaroo' '帝力' '星得斯/sidus wine'\n",
      " '佳沃/joyvio' '玛茜/rochemazet' '佰铄' '德森森/dr.zenzen' '卡斯特/kasteel '\n",
      " '罗蒂/laudi' '怡园酒庄/grace vineyard' '蓝仙姑' '澜' '西施佳雅' '露歌'\n",
      " '佳得美庄园/chateau cantemerle' '恋爱季/amore stagione'\n",
      " \"琴韵/melodiede l'accordeon\" '醉慕' '安徒生' '君顶' '五女山' '泸州老窖' '利腾/litten'\n",
      " '西莫/san simon' '维拉美伽/vila megal' '洛瑞斯/loris' '拉图嘉利' '加州乐事'\n",
      " '拉图雷蒙城堡/la figure ramon castle' '罗伯乐富齐/rupert & rothschild classique'\n",
      " '利达民/lindemans' '玛利亚之情' '玫瑰酒庄（无）' '音符/awjs' '红蔓庄园/tarapaca'\n",
      " '作品一号/opus one' '奥兰' '名仕罗纳德' '荣耀波尔图/royal oporto' '茅台/moutai '\n",
      " '法兰骑士/flangknight' '罗马假日' '梦坡' \"penfolds max's\" '璞立/beaulieu vineyard'\n",
      " '麦格根/mcguigan' '奔富蔻兰山/koonunga hill' 'dile' '金罐' '杜鹏特/les dupant'\n",
      " '云惜/yancy icewine' '卡维留里/cavicchioli' '小红帽/rotkappchen ' '爱佳诺' '洛神山庄'\n",
      " '卓林/zonin' '拉索尔菲/sol vin rouge' '巴图' '山地文/sandeman' '蒙大菲/robert mondavi'\n",
      " '禾富/blass' '图利斯' '云南红' '花思蝶/frescobaldi' '列吉塞/ridgeside winery' '彼得美德'\n",
      " '梦时刻/mosketto' '圣宁/saitnin' '圣骑庄园' '慕狮王子' '紫轩' 'vicente gandia' '伊拉苏'\n",
      " '卡特尔' '狄士美庄园/chateau desmirail' '龙徽' 'sunshinecreek' '意大利之花' '爱嗨/iheart'\n",
      " '法尔凯特/moscato dasti' '爱慕尔' '蓝海之鲸/mr.sparkling' '三只熊/three bears' '贺兰山'\n",
      " '优尼特/riunite' '莱恩格瑞' '火地岛' '帕尔曼/perlman' '伊村山野' 'berton vineyard'\n",
      " '驼铃/tuo ling' '佩高/chateau pegau' '光之颂亿/maison de grand esprit'\n",
      " '桌山/kaapzicht' '海列巴/khareba' '曼拉维/maanae' '玛拉尼/marani' '类人首' '尚尼酒庄' '拉罗兰'\n",
      " '慕拉 ' '银色高地' '酒司令/tamada' '托布雷' '克鲁斯大帝/reserve dellacroix' 'pasqua'\n",
      " '公牛血/egri bikaver' '首彩/handpicked' '歌思维亚/gesivlia' '奥玛/alma'\n",
      " '拿戈卢/la gloire' '升禧' 'tamaya' '汉森/hansen' '贾斯汀/divine justine' '图雷'\n",
      " '阿维娃/aviva' '美尼尔城堡' '蒙帕斯丽尔/moonpasiler' '卡梅罗西/comte rossi'\n",
      " '本格拉/benguela cove' '雅立/yali' '新天/suntime' '海豚鸟' '十字木桐' '卡聂高' '朵雅'\n",
      " '柯诺威庄园/cornowayfinca' '魔芳/platino' '开普山/cape mountain' '彭索酒庄' '昭雅' '唯乐'\n",
      " '希雅斯酒庄/xiyasiwinery' '醉鹅娘' '圣母之泉/blaue quelle' '芭诺斯' 'budureasca'\n",
      " '琳赛/lindsay wine estate' '卡伯纳' '詹姆士酒庄' '纳丹堡/nathenfort' '白马歌'\n",
      " '路易城堡/chateau louis' '波图/poto' '魔决' '盛纳/shengna' '梦陇/monlot'\n",
      " '拉梦堡/lamengbao' '艾维娜/invina' '狮堡/lion bourg' '宜兰树/vina inigo'\n",
      " '风时亚/franzia' '菲特瓦' '蜜黛/mythet' '龙船酒庄' '蒙佩奇古堡/chateau mont perat'\n",
      " '依拉苏/errazuriz']\n"
     ]
    }
   ],
   "source": [
    "# 处理红酒名称（统一规范中文/英文的格式）\n",
    "#去keyword中“红酒”字符\n",
    "df['keyword']=df['keyword'].str.split(' 红酒').str[0]\n",
    "\n",
    "#整理红酒品牌\n",
    "brand = pd.read_csv(\"./data/红酒品牌.csv\",header=None,encoding=\"utf-8\")\n",
    "brands =[]\n",
    "for k1,k2 in zip(list(brand[0]),list(brand[1])):\n",
    "    if pd.isnull(k2):\n",
    "        brands.append(k1)\n",
    "    else:\n",
    "        brands.append(k1+\"/\"+k2)\n",
    "        \n",
    "# 品牌替换\n",
    "def modify_keywords(w, lists):\n",
    "    for b in lists:\n",
    "        if w.strip() in b:\n",
    "            return b\n",
    "    return w.strip()\n",
    "\n",
    "\n",
    "df['keyword'] = df['keyword'].apply(modify_keywords, lists =brands)\n",
    "df['keyword']=df['keyword'].str.lower()#转化为小写\n",
    "\n",
    "print(df[\"keyword\"].unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "************************************************\n",
    "### 步鄹4:去除重复数据\n",
    "由于红酒品牌搜索时有中文和英文，可能会存在相同数据，根据url属性来判断两条数据是否重复。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 484,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "不存在重复数据\n",
      "(30985, 229)\n"
     ]
    }
   ],
   "source": [
    "#若爬取数据有相同的url，则认为是相同数据，只保留一条\n",
    "\n",
    "#数据是否有相同行，若有返回true，否则False\n",
    "if df.duplicated(subset=[\"url\"], keep=False).any(): \n",
    "    print(\"存在重复数据\")\n",
    "    df = df.drop_duplicates(subset=[\"url\"], keep=\"first\")\n",
    "else:\n",
    "    print(\"不存在重复数据\")\n",
    "print(df.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "************************************************\n",
    "### 步鄹5:过滤非红酒类商品\n",
    "\n",
    "观察数据，发现数据中还存在红酒杯、醒酒器黄酒等噪声数据，而“甜度”是红酒的一般特性，若无该属性则很大程度上是非红酒数据，可能酒具以及黄酒白酒等数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 485,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(18677, 229)\n"
     ]
    }
   ],
   "source": [
    "df = df.dropna(subset=[\"甜度\"])\n",
    "print(df.shape) #18677 299"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "************************************************\n",
    "### 步鄹6:过滤非750ml红酒数据并处理价格\n",
    "将删除“产品重量”属性，因为包装不同，礼盒或者整箱产品包装中带有赠品，酒杯酒具等物品，质量变化数据不可靠，难以清洗。\n",
    "\n",
    "统一红酒容量为750ml，并计算单瓶价格；继而可以删除标题，包装和容量三个属性列。\n",
    "\n",
    "据观察，包装和容量的形容多样，单位也多样，标题的可信度反而相对较高，所以我们从标题中匹配750ml的红酒数据。\n",
    "\n",
    "操作步骤如下：\n",
    "\n",
    "1、保留标题中含有750字样的数据；\n",
    "\n",
    "2、含有特殊的瓶数转换成相应的数字，转第4步；\n",
    "\n",
    "        若匹配到两次及两次以上，则排除该数据。\n",
    "        \n",
    "3、利用jieba分词工具提取标题中的数字，仅保留限定数值之内的数据：\n",
    "\n",
    "        若750后面的数字存在，则认为是瓶数，转第4步；\n",
    "        \n",
    "        若750前面的数字存在，则认为是瓶数，转第4步；\n",
    "        \n",
    "        若前后都没有限定数值之内的数字出现，判断是单瓶，转第4步；\n",
    "        \n",
    "        若750前后面的数字都存在，则排除该数据。\n",
    "        \n",
    "4、重新计算价格。\n",
    "\n",
    "具体代码如下："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 486,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(12948, 229)\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "c:\\users\\98657\\appdata\\local\\programs\\python\\python38\\lib\\site-packages\\pandas\\core\\indexing.py:670: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  iloc._setitem_with_indexer(indexer, value)\n"
     ]
    }
   ],
   "source": [
    "# 提取瓶数和重新计算价格\n",
    "#红酒商品描述中有含有的数字主要有三种年份、容量和瓶数，容量固定为750，主要识别瓶数\n",
    "\n",
    "# 挑出含750字样的数据\n",
    "df = df[df[\"name\"].str.contains(\"750\")]\n",
    "print(df.shape)\n",
    "\n",
    "#处理价格\n",
    "import jieba.posseg as pseg\n",
    "\n",
    "def get_numbers(words, num_type = \"int\"):\n",
    "    \"\"\"返回string中的数字\n",
    "    Args:\n",
    "        words: 字符串\n",
    "        num_type: 获取字符串中float型数字还是int型数字\n",
    "    \"\"\"\n",
    "    nums = []\n",
    "    for w,p in pseg.cut(words): #jieba词性标注不能将“750ml*6\"中的6识别为数字，换一种方式\n",
    "        if num_type == \"int\":\n",
    "            try: nums.append(int(w))\n",
    "            except:continue\n",
    "        if num_type == \"float\":\n",
    "            try: nums.append(float(w))\n",
    "            except:continue\n",
    "    return nums\n",
    "\n",
    "wine_bottles = {}          #存放红酒瓶数字典\n",
    "with open(\"./dict/wine_bottles.txt\",\"r\",encoding = \"utf-8\") as f:\n",
    "    for line in f.readlines():\n",
    "        k,v = line.strip().split(\" \")\n",
    "        wine_bottles[k]=v\n",
    "\n",
    "df_index = df.index.tolist()  #获取df索引列表\n",
    "df[\"price\"] = df[\"price\"].astype(\"float\") #更改数据类型\n",
    "\n",
    "for i, name in zip(df_index, df[\"name\"]):\n",
    "    bottles = [int(wine_bottles[k])for k in wine_bottles if name.find(k) != -1]    #\n",
    "    if len(bottles) == 1:\n",
    "        df['price'].loc[i] = float('%.2f' % (df['price'].loc[i]/bottles[0]))\n",
    "        continue\n",
    "    elif len(bottles)>1:\n",
    "        df = df.drop(index = i,axis = 0)\n",
    "        continue\n",
    "        \n",
    "    numbers = get_numbers(name,\"int\")        #bottles==0，用jieba分词提取红酒标题中的数字\n",
    "    if 750 not in numbers:        #选取是含有750的样本，但结巴分词可能将750切成其他组合词，识别不出750这个数字，删除\n",
    "        df = df.drop(index = i,axis = 0)\n",
    "        continue\n",
    "    numbers = [n for n in get_numbers(name,\"int\") if n in [750,1,2,3,4,5,6,8,12]]        #提取标题中特定数字\n",
    "    if len(numbers) == numbers.count(750):         #只有750数字,认为是单瓶不处理\n",
    "        continue\n",
    "    if numbers.count(750) > 1:        #存在多个数字且750个数有多个，删除\n",
    "            df = df.drop(index = i,axis = 0)\n",
    "            continue\n",
    "    if numbers.index(750) == 0:        #存在多个数字，750只有一个，且750后面有数字,更改\n",
    "        df['price'].loc[i] = float('%.2f' % (df['price'].loc[i]/numbers[1]))\n",
    "    elif numbers.index(750) == -1:        #存在多个数字，750只有一个，且750前面有数字，更改\n",
    "        df['price'].loc[i] = float('%.2f' % (df['price'].loc[i]/numbers[-2]))\n",
    "    else:\n",
    "         df = df.drop(index = i,axis = 0)    #存在多个数字，750只有一个，且750前面后面都有数字，删除\n",
    "            \n",
    "df.drop([\"包装\",\"容量\",\"产品重量（kg）\"], axis=1, inplace=True)\n",
    "print(\"finished\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "************************************************\n",
    "### 步鄹7:过滤无用属性\n",
    "删除缺失值比较多的属性列，查看每列缺失值的数目，具体代码如下："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 487,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "每列缺失值个数:\n",
      "name              0\n",
      "sku_id            0\n",
      "id                0\n",
      "price             0\n",
      "shop_name         0\n",
      "              ...  \n",
      "开关类型          11611\n",
      "是否带光源         11611\n",
      "功能特性          11611\n",
      "固定方式          11611\n",
      "产品展示尺寸（cm）    11611\n",
      "Length: 226, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(\"每列缺失值个数:\")\n",
    "print(df.isnull().sum())#统计每列的缺失值个数"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "观察每列的缺失值数目，采用删除缺失值超过80%的列的策略"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 488,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 11611 entries, 1 to 30984\n",
      "Data columns (total 16 columns):\n",
      " #   Column   Non-Null Count  Dtype  \n",
      "---  ------   --------------  -----  \n",
      " 0   name     11611 non-null  object \n",
      " 1   price    11611 non-null  float64\n",
      " 2   keyword  11611 non-null  object \n",
      " 3   产区       11611 non-null  object \n",
      " 4   年份       11611 non-null  object \n",
      " 5   酒精度      11611 non-null  object \n",
      " 6   保质期      11611 non-null  object \n",
      " 7   存储方法     11611 non-null  object \n",
      " 8   类别       11571 non-null  object \n",
      " 9   葡萄品种     11611 non-null  object \n",
      " 10  甜度       11611 non-null  object \n",
      " 11  口感       11611 non-null  object \n",
      " 12  颜色       11611 non-null  object \n",
      " 13  原产地      11611 non-null  object \n",
      " 14  特性       11494 non-null  object \n",
      " 15  国产/进口    11611 non-null  object \n",
      "dtypes: float64(1), object(15)\n",
      "memory usage: 1.5+ MB\n"
     ]
    }
   ],
   "source": [
    "#删除列数据，thresh作用：保留至少有2329（11648*20%）个非 NA 数的列\n",
    "df = df.dropna(axis=1,thresh=2329)\n",
    "# 再删除[\"id\",\"shop_id\",\"shop_name\",\"sku_id\",\"url\"]\n",
    "df.drop([\"id\",\"shop_id\",\"shop_name\",\"sku_id\",\"url\"],axis=1,inplace=True)\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 298,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv(\"draft-processed.csv\",encoding=\"utf-8_sig\",index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "************************************************\n",
    "## 六、实验总结\n",
    "本节我们基于pandas工具对爬取的数据进行格式和属性层面的清洗，包含以下步骤：\n",
    "\n",
    "1.数据格式的整理\n",
    "\n",
    "2.去除重复数据\n",
    "\n",
    "3.排除非红酒类商品\n",
    "\n",
    "4.计算单价\n",
    "\n",
    "5.删除无关属性\n",
    "\n",
    "在下节中我们将对空值属性进行处理。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*****************************************************************\n",
    "************************************************************\n",
    "## 实验3-2 处理空值的属性数据\n",
    "### 一、实验目的\n",
    "\n",
    "了解pandas的常用函数\n",
    "\n",
    "了解数据清洗的基础方法\n",
    "\n",
    "### 二、实验环境\n",
    "\n",
    "Python3开发环境，第三方包有pandas\n",
    "\n",
    "### 三、实验原理\n",
    "\n",
    "本实验将利用pandas该python第三方包对爬取的数据进行清洗。pandas 是基于NumPy 的一种工具，该工具是为了解决数据分析任务而创建的。Pandas 纳入了大量库和一些标准的数据模型，提供了高效地操作大型数据集所需的工具。pandas提供了大量能使我们快速便捷地处理数据的函数和方法。你很快就会发现，它是使Python成为强大而高效的数据分析环境的重要因素之一。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 四、实验步骤\n",
    "******************************************************\n",
    "### 步鄹1:处理空值\n",
    "\n",
    "主要是处理      类别    和    特性    缺失值用   众数    代替\n",
    "并查看有哪些属性是存在缺失的\n",
    "\n",
    "具体代码如下："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 489,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "name         0\n",
       "price        0\n",
       "keyword      0\n",
       "产区           0\n",
       "年份           0\n",
       "酒精度          0\n",
       "保质期          0\n",
       "存储方法         0\n",
       "类别          40\n",
       "葡萄品种         0\n",
       "甜度           0\n",
       "口感           0\n",
       "颜色           0\n",
       "原产地          0\n",
       "特性         117\n",
       "国产/进口        0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 489,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "********************************************************\n",
    "### 步鄹2:替换值筛选\n",
    "讲解：可见，本节我们需要处理的是特性和类别两个字段，由下图中的聚合语句结果中可发现两个字段都是有限种取值，即离散值，且考虑到同一品牌的红酒的特性、类别极大可能是相同的，因而采用众数替代的方法来处理缺失值，即拿同一品牌的红酒的特性、类别出现最多的值最为替代值。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 490,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "特性\n",
       "普通餐酒               6103\n",
       "精品葡萄酒              1561\n",
       "列级庄                1443\n",
       "法定产区酒（AOC/AOP等）     957\n",
       "名庄葡萄酒               586\n",
       "中级庄                 578\n",
       "酒杯/酒具               166\n",
       "有机葡萄酒                76\n",
       "AOC/AOP              24\n",
       "dtype: int64"
      ]
     },
     "execution_count": 490,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(\"特性\").size().sort_values(ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 步鄹3:众数替代缺失值\n",
    "    \n",
    "    因为缺失值比较少，所以不需要处理的太仔细，不会对数据集产生严重的影响\n",
    "    具体代码如下："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 491,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 众数替代缺失值\n",
    "\n",
    "#上述统计结果可以看出“特性”和“类别”出现缺失值\n",
    "def process_nan(col,gp_col,df):\n",
    "    #计算该列分组众数,可能出现某个品牌的众数为nan，以“no match”代替\n",
    "    df_mode = df.groupby(gp_col)[col].agg(lambda x: next(iter(x.value_counts().index), 'no match'))\n",
    "    df[col] = df[col].fillna(df[gp_col].map(df_mode))\n",
    "    df = df[~df[col].isin([\"no match\"])] #删除“no match”\n",
    "    return df\n",
    "    \n",
    "df = process_nan(\"特性\", \"keyword\", df)\n",
    "df = process_nan(\"类别\", \"keyword\", df)\n",
    "\n",
    "# 保存数据\n",
    "df.to_csv(\"pre-processed.csv\",encoding=\"utf-8_sig\",index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 四、知识回顾\n",
    "如上述代码所示，我们保存下来 pre-processed.csv 文件，下节讲述利用pandaBI观察数据再对属性作调整。\n",
    "\n",
    "通过两节课程的学习，我们把源数据经过数据清洗工作暂时生成文件 pre-processed.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 五、实验总结\n",
    "上节我们基于pandas工具对爬取的数据进行格式和属性层面的清洗，本节在此基础上进行对空值进行处理，下节我们将学习通过工具pandaBI观察数据并整理数据。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "************************************************************\n",
    "************************************************************\n",
    "## 实验3-3 利用pandaBI查看数据各个维度的分布\n",
    "### 一、实验目的\n",
    "了解PandaBI的操作流程\n",
    "了解数据清洗的基础方法\n",
    "\n",
    "### 二、实验环境\n",
    "Python3开发环境，第三方包有pandas，工具有PandaBI\n",
    "\n",
    "### 三、实验原理\n",
    "本实验利用PandaBI可视化工具观察数据，并依然利用pandas工具对数据进行再清洗。\n",
    "### 四、实验步骤\n",
    "可利用pandas包中保存函数，将3.2节的最终处理结果进行保存为 pre-processed.csv，结合pandaBI可视化工具观察数据各个维度分布情况再做调整。\n",
    "本章首先简单介绍pandaBI的使用流程，再根据可视化结果对数据进行调整\n",
    "如实验手册所示：pandaBI主要有以下四个模块：\n",
    "\n",
    "数据大屏：用来做大屏展现（多个可视化分析结果的组合展现）\n",
    "\n",
    "仪表盘：可视化结果展示（可用作页面嵌入）\n",
    "\n",
    "工作表：进行多数据源的组合，也可以是单表数据\n",
    "\n",
    "数据源：支持多数据源的导入\n",
    "### 步鄹1:pandaBI的使用流程\n",
    "step1:导入数据\n",
    "\n",
    "进入数据源模块  =>  添加数据源 =>  选择csv  =>  填写数据源相关信息\n",
    "\n",
    "请参考实验手册：\n",
    "\n",
    "step2：构建数据表\n",
    "\n",
    "进入工作表模块，我们只有一张表，不需要和其他表做连接，所以只要新建工作表，拖拽出刚才的数据源，然后保存即可。\n",
    "\n",
    "请参考实验手册：\n",
    "\n",
    "step3：可视化分析\n",
    "\n",
    "进入仪表盘，即时可以开始进行对字段分析和探索了，数据大屏的展示和使用将在3.3节讲解。\n",
    "\n",
    "请参考实验手册："
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "******************************************************\n",
    "### 步鄹2:pandaBI观察各维度特征再处理\n",
    "\n",
    "- 存储方法：大部分数据是形容不能避光阴凉的不同说法，选择删除\n",
    "\n",
    "- 原产地，甜度：含有部分取值为“其他”的数据，先从标题中找关键词，再采用同一红酒品牌的众数替代策略予以调整\n",
    "- 颜色 ：通过对数据的分布，红葡萄酒最多的是宝石红,并且占其他中的90%以上，所以我们只对红葡萄酒的其他做改变\n",
    "- 类别，葡萄品种：采用onehot编码的理论思想对数据进行转换\n",
    "\n",
    "- 不可以（9999太多了） 年份：数据中包含有很多“以实物为准”，“见瓶身”这种取值，但我们想知道的是酒的年龄，处理方式是利用文本处理的分词技术提取其中的年份，再与当前年份相减；部分数据的年份给的是一个范围值，此时取平均值；没有年份则用9999代替。\n",
    "\n",
    "- 同上 酒精度：同“年份”属性一样，有数据不规范，“实物为准”的取值问题，同样采用文本处理的分词技术提取酒精度的数值。\n",
    "- 保质期：书写格式非常不统一，有些是按天为单位，有些是按年为单位，且有数字有中文的不同表达，该属性予以删除。\n",
    "\n",
    "******************************************************"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 492,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 先处理原产地和甜度\n",
    "#df = pd.read_csv(\"pre-processed.csv\")\n",
    "#df.drop([\"保质期\",\"存储方法\"], axis=1, inplace=True)\n",
    "c_list = list(df['原产地'].unique())\n",
    "b_list = ['半干','干','半甜','甜']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 494,
   "metadata": {},
   "outputs": [],
   "source": [
    "_c = df[df['原产地']=='其它']\n",
    "_b = df[df['甜度']=='其它']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 496,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = df.copy()\n",
    "#先根据name来一次\n",
    "c_list.append('罗马尼亚')\n",
    "c_list.append('匈牙利')\n",
    "c_list.append('澳洲')\n",
    "\n",
    "for index, row in _c.iterrows():\n",
    "    for i in c_list:                      #原产地\n",
    "        if str(row['name']).find(i)!= -1:\n",
    "            df1.at[index,'原产地'] = i\n",
    "            break\n",
    "df1[df1['原产地']=='澳洲']['原产地']\n",
    "df1.loc[df1['原产地'] == '澳洲', ['原产地']] = '澳大利亚'\n",
    "\n",
    "t = []\n",
    "for index, row in _b.iterrows():       #甜度\n",
    "    for i in b_list:\n",
    "        if str(row['name']).find(i)!= -1:\n",
    "            if i == '半干':\n",
    "                df1.at[index,'甜度'] = '半干型'\n",
    "                break\n",
    "            if i == '干':\n",
    "                df1.at[index,'甜度'] = '干型'\n",
    "                break\n",
    "            elif i=='半甜':\n",
    "                df1.at[index,'甜度'] = '半甜型'\n",
    "                break\n",
    "            else: \n",
    "                df1.at[index,'甜度'] = '甜型'\n",
    "                break\n",
    "\n",
    "\n",
    "df = df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 497,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "c:\\users\\98657\\appdata\\local\\programs\\python\\python38\\lib\\site-packages\\pandas\\core\\indexing.py:1745: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  isetter(ilocs[0], value)\n"
     ]
    }
   ],
   "source": [
    "df.drop([\"保质期\",\"存储方法\"], axis=1, inplace=True)\n",
    "# 处理 原产地，甜度\n",
    "def process_others(col, gp_col, df_other, df_mode,df):\n",
    "    other_series = df_other[col]\n",
    "    names = list(df.loc[other_series,gp_col])\n",
    "    t = df_mode.loc[names,col]\n",
    "    t.index = df.loc[other_series,col].index #col中含有“其它”的索引\n",
    "    df.loc[other_series,col] = t\n",
    "    return df\n",
    "\n",
    "cols = [\"原产地\",\"甜度\"] #需要处理的列,\"颜色\"\n",
    "gp_col = 'keyword'  #分组列\n",
    "df_mode = df.groupby('keyword').agg(lambda x: x.value_counts().index[0]) #根据keyword（品牌）分组计算众数\n",
    "\n",
    "for col in cols:\n",
    "    df_other = pd.DataFrame(df[col]==\"其它\") #col中存在“其它”的行\n",
    "    df = process_others(col, gp_col, df_other, df_mode, df)\n",
    "    #因为某品牌红酒在某列上的众数可能是“其它”，经过众数替换后再删除仍存在的“其它”的行\n",
    "    df = df[~df[col].isin([\"其它\"])] #通过~取反，选取col中不包含“其它”的行"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*******************************************************\n",
    "### 步鄹3:处理特征类别\n",
    "本节以”葡萄品种“，”酒精度“为例，讲解处理过程，其他属性的处理在前面小节的处理流程中均有涉及，学生可自行完成。\n",
    "\n",
    "1. 处理 葡萄品种 的操作流程如下：\n",
    "\n",
    "  1）获取所有的葡萄品种类别\n",
    "\n",
    "  2）对每个葡萄品种新增一维来表示，假设共有6种葡萄品种，则需要6维来表示该属性值\n",
    "  \n",
    "具体代码如下："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 418,
   "metadata": {},
   "outputs": [],
   "source": [
    "#关联度计算\n",
    "def cal_corr(qt):\n",
    "    le = LabelEncoder()\n",
    "    col = qt.columns\n",
    "    for c in col:\n",
    "        qt[c]=le.fit_transform(qt[c].astype(str))\n",
    "    return qt.corr()\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 498,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 处理 类别\n",
    "# list(df.groupby(\"类别\").count().index)\n",
    "lbs = [\"冰酒/贵腐/甜酒\",\"白葡萄酒\",\"果味葡萄酒\",\"桃红葡萄酒\",\"起泡酒/香槟\",\"红葡萄酒\"]\n",
    "\n",
    "df[\"冰酒/贵腐/甜酒\"] = df['类别'].apply(lambda x : 1 if str(x).find(\"冰酒\") != -1 or str(x).find(\"贵腐\")!= -1 or str(x).find(\"甜酒\")!= -1 else 0)\n",
    "df[\"白葡萄酒\"] = df['类别'].apply(lambda x : 1 if str(x).find(\"白葡萄酒\") != -1 else 0)\n",
    "df[\"果味葡萄酒\"] = df['类别'].apply(lambda x : 1 if str(x).find(\"果味葡萄酒\") != -1 else 0)\n",
    "df[\"桃红葡萄酒\"] = df['类别'].apply(lambda x : 1 if str(x).find(\"桃红葡萄酒\") != -1 else 0)\n",
    "df[\"红葡萄酒\"] = df['类别'].apply(lambda x : 1 if str(x).find(\"红葡萄酒\") != -1 else 0)\n",
    "df[\"起泡酒/香槟\"] = df['类别'].apply(lambda x : 1 if str(x).find(\"起泡酒\") != -1 or str(x).find(\"香槟\") != -1 else 0)\n",
    "\n",
    "# df[['类别']+lbs].head\n",
    "df.drop(\"类别\", axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 501,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "宝石红    8276\n",
       "石榴红     978\n",
       "砖红色     625\n",
       "透明      323\n",
       "柠檬黄     224\n",
       "紫色      221\n",
       "琥珀      151\n",
       "粉色      140\n",
       "其它      135\n",
       "金色      102\n",
       "褐色       83\n",
       "淡绿       48\n",
       "柠檬绿      43\n",
       "茶色       11\n",
       "橙色        9\n",
       "鲑鱼色       1\n",
       "Name: 颜色, dtype: int64"
      ]
     },
     "execution_count": 501,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#通过对数据的观察，我们可以发现颜色和类别相关，而且大多数颜色为其他的时候类别都是红葡萄酒，而红葡萄酒类中‘宝石红’出现的频率最高，所以我们将宝石红填入红葡萄酒类中\n",
    "\n",
    "_a = df[df['颜色']=='其它']\n",
    "for index, row in _a.iterrows():       #颜色\n",
    "    if row['红葡萄酒']==1:\n",
    "        df.at[index,'颜色']='宝石红'\n",
    "    \n",
    "df['颜色'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 502,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "其它                                                                                      4614\n",
       "赤霞珠（Cabernet Sauvignon）                                                                 1808\n",
       "西拉/设拉子（Syrah/Shiraz）                                                                     661\n",
       "赤霞珠（Cabernet Sauvignon）|梅洛（Merlot）                                                       546\n",
       "梅洛（Merlot）                                                                               405\n",
       "                                                                                        ... \n",
       "品丽珠（Cabernet Franc）|赤霞珠（Cabernet Sauvignon）|梅洛（Merlot）                                     1\n",
       "赤霞珠（Cabernet Sauvignon）|西拉/设拉子（Syrah/Shiraz）|霞多丽（Chardonnay）|长相思（Sauvignon Blanc）|其它       1\n",
       "西拉/设拉子（Syrah/Shiraz）|品丽珠（Cabernet Franc）                                                   1\n",
       "仙粉黛（Zinfandel）|赤霞珠（Cabernet Sauvignon）                                                     1\n",
       "西拉/设拉子（Syrah/Shiraz）|马尔贝克（Malbec）|其它                                                       1\n",
       "Name: 葡萄品种, Length: 215, dtype: int64"
      ]
     },
     "execution_count": 502,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#大量存在只有其他的情况，可以看作是missing value,因为他不能给数据集提供信息，会对数据集造成影响\n",
    "\n",
    "df['葡萄品种'].value_counts() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 514,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "葡萄品种                       1.000000\n",
       "红葡萄酒                       0.492333\n",
       "霞多丽（Chardonnay）            0.482698\n",
       "白葡萄酒                       0.384381\n",
       "黑皮诺（Pinot Noir）            0.372433\n",
       "长相思（Sauvignon Blanc）       0.222253\n",
       "马尔贝克（Malbec）               0.209641\n",
       "雷司令（Riesling）              0.175106\n",
       "西拉/设拉子（Syrah/Shiraz）       0.124197\n",
       "颜色                         0.076774\n",
       "其它                         0.068341\n",
       "佳美娜（Carmenere）             0.058984\n",
       "品丽珠（Cabernet Franc）        0.048342\n",
       "起泡酒/香槟                     0.047492\n",
       "桑娇维塞（Sangiovese）           0.046007\n",
       "梅洛（Merlot）                 0.041163\n",
       "蛇龙珠（Cabernet Gernischt）    0.036651\n",
       "桃红葡萄酒                      0.028987\n",
       "仙粉黛（Zinfandel）             0.028488\n",
       "赤霞珠（Cabernet Sauvignon）    0.026577\n",
       "国产/进口                      0.023954\n",
       "酒精度                        0.020254\n",
       "口感                         0.018276\n",
       "产区                         0.017971\n",
       "冰酒/贵腐/甜酒                   0.015432\n",
       "果味葡萄酒                      0.014833\n",
       "匹诺塔吉（Pinotage）             0.013049\n",
       "price                      0.011738\n",
       "原产地                        0.010432\n",
       "特性                         0.010408\n",
       "佳美（Gamay）                  0.006997\n",
       "keyword                    0.006253\n",
       "内比奥罗（Nebbiolo）             0.005058\n",
       "name                       0.003674\n",
       "年份                         0.003326\n",
       "甜度                         0.001251\n",
       "Name: 葡萄品种, dtype: float64"
      ]
     },
     "execution_count": 514,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "qt = df[df['葡萄品种']!='其它']\n",
    "\n",
    "#corrMatrix\n",
    "cal_corr(qt.copy())['葡萄品种'].abs().sort_values(ascending=False)  #关联度看出葡萄品种和葡萄酒种类是相关的\n",
    "#可以看出关联度最高的是红葡萄酒和白葡萄酒，这两个量对葡萄品种影响较大，所以我们只思考这两个因素"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 516,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "霞多丽（Chardonnay）                                                                                                                   276\n",
      "赤霞珠（Cabernet Sauvignon）                                                                                                           231\n",
      "雷司令（Riesling）                                                                                                                      40\n",
      "长相思（Sauvignon Blanc）                                                                                                               39\n",
      "赤霞珠（Cabernet Sauvignon）|梅洛（Merlot）|霞多丽（Chardonnay）|雷司令（Riesling）|佳美（Gamay）|其它                                                      16\n",
      "长相思（Sauvignon Blanc）|其它                                                                                                            14\n",
      "赤霞珠（Cabernet Sauvignon）|梅洛（Merlot）|西拉/设拉子（Syrah/Shiraz）|其它                                                                         12\n",
      "佳美（Gamay）                                                                                                                          11\n",
      "梅洛（Merlot）                                                                                                                         11\n",
      "赤霞珠（Cabernet Sauvignon）|梅洛（Merlot）|霞多丽（Chardonnay）                                                                                 11\n",
      "西拉/设拉子（Syrah/Shiraz）                                                                                                               11\n",
      "赤霞珠（Cabernet Sauvignon）|梅洛（Merlot）|西拉/设拉子（Syrah/Shiraz）|黑皮诺（Pinot Noir）|佳美娜（Carmenere）|霞多丽（Chardonnay）|长相思（Sauvignon Blanc）|其它     10\n",
      "赤霞珠（Cabernet Sauvignon）|其它                                                                                                          9\n",
      "霞多丽（Chardonnay）|其它                                                                                                                  8\n",
      "雷司令（Riesling）|其它                                                                                                                    7\n",
      "赤霞珠（Cabernet Sauvignon）|梅洛（Merlot）|西拉/设拉子（Syrah/Shiraz）                                                                             7\n",
      "赤霞珠（Cabernet Sauvignon）|梅洛（Merlot）|西拉/设拉子（Syrah/Shiraz）|黑皮诺（Pinot Noir）|马尔贝克（Malbec）|佳美娜（Carmenere）|霞多丽（Chardonnay）                 7\n",
      "赤霞珠（Cabernet Sauvignon）|梅洛（Merlot）|佳美娜（Carmenere）|霞多丽（Chardonnay）|其它                                                                6\n",
      "梅洛（Merlot）|黑皮诺（Pinot Noir）|雷司令（Riesling）                                                                                            6\n",
      "赤霞珠（Cabernet Sauvignon）|西拉/设拉子（Syrah/Shiraz）                                                                                        6\n",
      "赤霞珠（Cabernet Sauvignon）|梅洛（Merlot）|霞多丽（Chardonnay）|其它                                                                               6\n",
      "赤霞珠（Cabernet Sauvignon）|梅洛（Merlot）|西拉/设拉子（Syrah/Shiraz）|霞多丽（Chardonnay）                                                             5\n",
      "赤霞珠（Cabernet Sauvignon）|梅洛（Merlot）|黑皮诺（Pinot Noir）|霞多丽（Chardonnay）                                                                  5\n",
      "赤霞珠（Cabernet Sauvignon）|梅洛（Merlot）|马尔贝克（Malbec）|长相思（Sauvignon Blanc）|其它                                                             5\n",
      "赤霞珠（Cabernet Sauvignon）|西拉/设拉子（Syrah/Shiraz）|其它                                                                                     4\n",
      "黑皮诺（Pinot Noir）|霞多丽（Chardonnay）                                                                                                     4\n",
      "赤霞珠（Cabernet Sauvignon）|梅洛（Merlot）|品丽珠（Cabernet Franc）                                                                              4\n",
      "赤霞珠（Cabernet Sauvignon）|西拉/设拉子（Syrah/Shiraz）|霞多丽（Chardonnay）|长相思（Sauvignon Blanc）|匹诺塔吉（Pinotage）                                    4\n",
      "赤霞珠（Cabernet Sauvignon）|黑皮诺（Pinot Noir）|佳美娜（Carmenere）|长相思（Sauvignon Blanc）                                                         4\n",
      "赤霞珠（Cabernet Sauvignon）|西拉/设拉子（Syrah/Shiraz）|霞多丽（Chardonnay）                                                                        3\n",
      "蛇龙珠（Cabernet Gernischt）                                                                                                             3\n",
      "西拉/设拉子（Syrah/Shiraz）|其它                                                                                                             2\n",
      "赤霞珠（Cabernet Sauvignon）|梅洛（Merlot）|雷司令（Riesling）                                                                                    2\n",
      "霞多丽（Chardonnay）|长相思（Sauvignon Blanc）                                                                                                2\n",
      "霞多丽（Chardonnay）|长相思（Sauvignon Blanc）|其它                                                                                             2\n",
      "赤霞珠（Cabernet Sauvignon）|马尔贝克（Malbec）                                                                                                2\n",
      "黑皮诺（Pinot Noir）|长相思（Sauvignon Blanc）|其它                                                                                             1\n",
      "马尔贝克（Malbec）|其它                                                                                                                     1\n",
      "赤霞珠（Cabernet Sauvignon）|梅洛（Merlot）                                                                                                  1\n",
      "赤霞珠（Cabernet Sauvignon）|霞多丽（Chardonnay）                                                                                             1\n",
      "品丽珠（Cabernet Franc）|其它                                                                                                              1\n",
      "雷司令（Riesling）|内比奥罗（Nebbiolo）                                                                                                        1\n",
      "赤霞珠（Cabernet Sauvignon）|梅洛（Merlot）|马尔贝克（Malbec）|佳美娜（Carmenere）|霞多丽（Chardonnay）|长相思（Sauvignon Blanc）                                 1\n",
      "赤霞珠（Cabernet Sauvignon）|雷司令（Riesling）                                                                                               1\n",
      "黑皮诺（Pinot Noir）|霞多丽（Chardonnay）|雷司令（Riesling）                                                                                       1\n",
      "Name: 葡萄品种, dtype: int64\n",
      "-------------------------------\n",
      "赤霞珠（Cabernet Sauvignon）                                                        5961\n",
      "西拉/设拉子（Syrah/Shiraz）                                                            656\n",
      "赤霞珠（Cabernet Sauvignon）|梅洛（Merlot）                                              546\n",
      "梅洛（Merlot）                                                                      401\n",
      "赤霞珠（Cabernet Sauvignon）|其它                                                      344\n",
      "                                                                               ... \n",
      "赤霞珠（Cabernet Sauvignon）|梅洛（Merlot）|西拉/设拉子（Syrah/Shiraz）|品丽珠（Cabernet Franc）       1\n",
      "匹诺塔吉（Pinotage）|其它                                                                 1\n",
      "梅洛（Merlot）|长相思（Sauvignon Blanc）                                                   1\n",
      "黑皮诺（Pinot Noir）|霞多丽（Chardonnay）|长相思（Sauvignon Blanc）|其它                           1\n",
      "赤霞珠（Cabernet Sauvignon）|马尔贝克（Malbec）|品丽珠（Cabernet Franc）                          1\n",
      "Name: 葡萄品种, Length: 202, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(qt[qt['白葡萄酒']==1]['葡萄品种'].value_counts())\n",
    "print(\"-------------------------------\")\n",
    "print(qt[qt['红葡萄酒']==1]['葡萄品种'].value_counts())\n",
    "\n",
    "#运行后可以看出白葡萄酒大概率是霞多丽，红葡萄酒大概率是赤霞珠"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 517,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "292"
      ]
     },
     "execution_count": 517,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#霞多丽（Chardonnay）赤霞珠（Cabernet Sauvignon）\n",
    "df.loc[(df['葡萄品种'] == '其它') & (df['红葡萄酒'] == 1), ['葡萄品种']] = '赤霞珠（Cabernet Sauvignon）'\n",
    "df.loc[(df['葡萄品种'] == '其它') & (df['白葡萄酒'] == 1), ['葡萄品种']] = '霞多丽（Chardonnay）'\n",
    "len(df[df['葡萄品种']=='其它']) #只有292条其他了，可以接受"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*************************************************\n",
    "### 步鄹4:处理葡萄品种\n",
    "具体代码如下：   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 518,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['其它', '黑皮诺（Pinot Noir）', '霞多丽（Chardonnay）', '赤霞珠（Cabernet Sauvignon）', '雷司令（Riesling）', '桑娇维塞（Sangiovese）', '蛇龙珠（Cabernet Gernischt）', '匹诺塔吉（Pinotage）', '佳美娜（Carmenere）', '品丽珠（Cabernet Franc）', '西拉/设拉子（Syrah/Shiraz）', '内比奥罗（Nebbiolo）', '梅洛（Merlot）', '仙粉黛（Zinfandel）', '马尔贝克（Malbec）', '长相思（Sauvignon Blanc）', '佳美（Gamay）']\n"
     ]
    }
   ],
   "source": [
    "# 处理葡萄品种\n",
    "## 获取葡萄的所有品种类别\n",
    "tmp = list(df.groupby(\"葡萄品种\").count().index)\n",
    "graps = []\n",
    "for i in tmp:\n",
    "    graps += i.split(\"|\")\n",
    "graps = list(set(graps))\n",
    "print(graps)\n",
    "del tmp\n",
    "\n",
    "## 处理每个葡萄品种\n",
    "for j in graps:\n",
    "    df[j] = df['葡萄品种'].apply(lambda x : 1 if str(x).find(j) != -1 else 0)\n",
    "    \n",
    "# print(df[graps+['葡萄品种']].head)\n",
    "#df.drop(\"葡萄品种\", axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "********************************************************\n",
    "### 步鄹5:处理年份\n",
    "具体代码如下："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 519,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6757"
      ]
     },
     "execution_count": 519,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import numpy as np\n",
    "#处理年份,\n",
    "#可能出现的数字有：年份(2005,2012等)，年份区间（2016-2018等），年月日(2015.08.06,2012.05等),年数(0,1,3等),年数区间（0-3等，1-3等）\n",
    "       \n",
    "def deal_year(x):\n",
    "    numbers = []\n",
    "    for w,p in pseg.cut(x):\n",
    "        if p=='m':\n",
    "            try:numbers.append(int(w))\n",
    "            except:continue\n",
    "    if len(numbers) == 0:\n",
    "        return -1   #年份中无数字，以-1代替\n",
    "    years=[]\n",
    "    other_nums=[]\n",
    "    for n in numbers:\n",
    "        if n >1800 and n < 2020:\n",
    "            years.append(n)\n",
    "        elif n<50:\n",
    "            other_nums.append(n)\n",
    "        else:\n",
    "            continue\n",
    "    if years:\n",
    "        return 2019 - int(np.mean(years))\n",
    "    elif other_nums:\n",
    "        return int(np.mean(other_nums))\n",
    "    else:\n",
    "        return -1 #无合理数字，以-1代替    \n",
    "df[\"year\"] = df[\"年份\"].map(deal_year)\n",
    "len(df[df[\"year\"]==-1]) \n",
    "\n",
    "#有6700+条是无效数据，所以要做进一步处理"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "******************************************************\n",
    "### 步鄹6:处理酒精度\n",
    "具体代码如下："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 520,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3783"
      ]
     },
     "execution_count": 520,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 处理酒精度\n",
    "def deal_alcohol(x):\n",
    "    numbers = []\n",
    "    for w,p in pseg.cut(x):\n",
    "        if p=='m':\n",
    "            try:numbers.append(float(w))\n",
    "            except:continue\n",
    "    if len(numbers) > 1:\n",
    "        return np.mean(numbers)\n",
    "    elif len(numbers) == 1:\n",
    "        return numbers[0]\n",
    "    else:\n",
    "        return -1  # 酒精中无数字，以-1代替\n",
    "        \n",
    "df[\"alcohol\"] = df[\"酒精度\"].map(deal_alcohol)\n",
    "#无效数据过多需要做下一步处理\n",
    "\n",
    "len(df[df[\"alcohol\"]==-1]) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv(\"prepare_for_weibo.csv\",encoding=\"utf-8_sig\",index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***************************************************\n",
    "## 五、知识回顾\n",
    "综上，我们把数据清理完成，学生可后续再做些异常点处理的清洗工作，本案例暂不涉及。下一节处理标签，对价格区间进行定义和设置。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*********************************************\n",
    "## 六、实验总结\n",
    "本节利用pandaBI可视化工具对数据进行再观察，对相关特征进行再清洗。下一节将对价格区间进行确定。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***********************************************************\n",
    "***********************************************************\n",
    "# 实验3-4 定义红酒的价格区间\n",
    "## 一、实验目的\n",
    "了解pandas的常用函数\n",
    "\n",
    "了解数据清洗的基础方法\n",
    "\n",
    "## 二、实验环境\n",
    "Python3开发环境，第三方包有pandas\n",
    "\n",
    "## 三、实验原理\n",
    "本实验主要是为了确定红酒的价格区间，利用不同的分箱策略对红酒价格区间进行划分，此划分结果关系到下节的分类结果，可综合两者的效果对价格区间进行调整。\n",
    "## 四、实验步骤\n",
    "### 步鄹1:查看一下红酒的价格最小值和最大值\n",
    "具体代码如下："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(df[\"price\"].min())\n",
    "print(df[\"price\"].max())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***********************************************\n",
    "### 步鄹2:三种方式对价格的划分\n",
    "查看数值分布数量的常见方式有三种：等宽分箱、等频分箱和基于聚类的分箱\n",
    "代码如下："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 三种方式对价格的划分\n",
    "from sklearn.cluster import KMeans\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline\n",
    "plt.rcParams['font.family'] = ['sans-serif']\n",
    "plt.rcParams['font.sans-serif'] = ['SimHei']\n",
    "\n",
    "k = 8\n",
    "kmodel = KMeans(n_clusters = k,n_jobs=5)\n",
    "\n",
    "fig,ax= plt.subplots(1,3,figsize=(20,5))\n",
    "cat = pd.cut(df['price'],k)\n",
    "cat2 = pd.qcut(df['price'],k)\n",
    "kmodel.fit(df['price'].values.reshape(len(df),1))\n",
    "c = pd.DataFrame(kmodel.cluster_centers_).sort_values(0)\n",
    "w = c.rolling(2).mean().iloc[1:]\n",
    "w = [0] + list(w[0]) + [df['price'].max()] \n",
    "cat3 = pd.cut(df['price'], w)\n",
    "\n",
    "cat.value_counts(sort = False).plot.bar(grid= True,ax=ax[0],title = '等宽分箱')\n",
    "cat2.value_counts(sort = False).plot.bar(grid= True,ax=ax[1],title = '等频分箱')\n",
    "cat3.value_counts(sort = False).plot.bar(grid= True,ax=ax[2],title = 'KMeans聚类分箱')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*******************************************************\n",
    "### 步鄹3:等频分箱的实现\n",
    "根据上述三种价格区间的划分，选择“等频分箱”方法，由于最后一组价格区间（499,79560）跨度过大，对价格在500以上和500以下的重新分箱。\n",
    "本案例中自定义分成8个价格区间（观察下来很多属性有6-8种取值），因数据主要集中分布在500之前的数据，故设计前500包含5个区间，大于500的再设立3个区间。（注意：价格区间的个数比较自由化，可根据真实项目中的测试结果进行调整）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df3 = df[df[\"price\"]>500]\n",
    "fig,ax= plt.subplots(1,1,figsize=(20,5))\n",
    "cat4 = pd.qcut(df3['price'],3)\n",
    "cat4.value_counts(sort = False).plot.bar(grid= True,title = '等频分箱')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df4 = df[df[\"price\"]<=500]\n",
    "fig,ax= plt.subplots(1,1,figsize=(20,5))\n",
    "cat4 = pd.qcut(df4['price'],5)\n",
    "cat4.value_counts(sort = False).plot.bar(grid= True,title = '等频分箱')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***************************************************\n",
    "### 步鄹4:处理价格区间\n",
    "结合上述统计结果：将价格定位成以下8个区间：[0,50],[50-100],[100-150],[150-250],[250-500],[500-1000],[1000-2000],[2000-MAX]\n",
    "处理代码如下："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 406,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>price</th>\n",
       "      <th>keyword</th>\n",
       "      <th>产区</th>\n",
       "      <th>年份</th>\n",
       "      <th>酒精度</th>\n",
       "      <th>葡萄品种</th>\n",
       "      <th>甜度</th>\n",
       "      <th>口感</th>\n",
       "      <th>颜色</th>\n",
       "      <th>...</th>\n",
       "      <th>品丽珠（Cabernet Franc）</th>\n",
       "      <th>西拉/设拉子（Syrah/Shiraz）</th>\n",
       "      <th>内比奥罗（Nebbiolo）</th>\n",
       "      <th>梅洛（Merlot）</th>\n",
       "      <th>仙粉黛（Zinfandel）</th>\n",
       "      <th>马尔贝克（Malbec）</th>\n",
       "      <th>长相思（Sauvignon Blanc）</th>\n",
       "      <th>佳美（Gamay）</th>\n",
       "      <th>year</th>\n",
       "      <th>alcohol</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>法国原瓶进口红酒 威埃特伯爵半干型红葡萄酒 整箱装750ml*6瓶</td>\n",
       "      <td>0-50</td>\n",
       "      <td>通化/tonghua</td>\n",
       "      <td>其它</td>\n",
       "      <td>以实物为准</td>\n",
       "      <td>12%vol</td>\n",
       "      <td>赤霞珠（Cabernet Sauvignon）</td>\n",
       "      <td>半干型</td>\n",
       "      <td>饱满</td>\n",
       "      <td>宝石红</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>长城（GreatWall）红酒 精制解百纳干红葡萄酒 整箱装750ml*6瓶</td>\n",
       "      <td>0-50</td>\n",
       "      <td>长城/greatwall</td>\n",
       "      <td>其它</td>\n",
       "      <td>以实物为准</td>\n",
       "      <td>12%</td>\n",
       "      <td>赤霞珠（Cabernet Sauvignon）</td>\n",
       "      <td>干型</td>\n",
       "      <td>柔和</td>\n",
       "      <td>宝石红</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>京东海外直采 干露阿根廷风之语马尔贝克干红葡萄酒 红酒 750ml 干露品牌 原瓶进口</td>\n",
       "      <td>50-100</td>\n",
       "      <td>干露/concha y toro</td>\n",
       "      <td>其它</td>\n",
       "      <td>2016</td>\n",
       "      <td>以实物为准</td>\n",
       "      <td>马尔贝克（Malbec）</td>\n",
       "      <td>干型</td>\n",
       "      <td>饱满</td>\n",
       "      <td>宝石红</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>法国进口红酒 拉菲珍宝（小拉菲）红葡萄酒 2015年 750ml</td>\n",
       "      <td>2000-9223372036854775807</td>\n",
       "      <td>拉菲/lafite</td>\n",
       "      <td>波尔多</td>\n",
       "      <td>2015</td>\n",
       "      <td>12.5%vol</td>\n",
       "      <td>赤霞珠（Cabernet Sauvignon）</td>\n",
       "      <td>干型</td>\n",
       "      <td>强劲</td>\n",
       "      <td>宝石红</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>12.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>法国原瓶进口红酒 山图（ShanTu）罗纳河谷AOP级混酿干型红 葡萄酒 （TU178） 7...</td>\n",
       "      <td>100-150</td>\n",
       "      <td>山图/shantu</td>\n",
       "      <td>其它</td>\n",
       "      <td>2016</td>\n",
       "      <td>14</td>\n",
       "      <td>西拉/设拉子（Syrah/Shiraz）|其它</td>\n",
       "      <td>干型</td>\n",
       "      <td>柔和</td>\n",
       "      <td>石榴红</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 38 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                name  \\\n",
       "1                  法国原瓶进口红酒 威埃特伯爵半干型红葡萄酒 整箱装750ml*6瓶   \n",
       "2             长城（GreatWall）红酒 精制解百纳干红葡萄酒 整箱装750ml*6瓶   \n",
       "4        京东海外直采 干露阿根廷风之语马尔贝克干红葡萄酒 红酒 750ml 干露品牌 原瓶进口   \n",
       "5                   法国进口红酒 拉菲珍宝（小拉菲）红葡萄酒 2015年 750ml   \n",
       "8  法国原瓶进口红酒 山图（ShanTu）罗纳河谷AOP级混酿干型红 葡萄酒 （TU178） 7...   \n",
       "\n",
       "                      price            keyword   产区     年份       酒精度  \\\n",
       "1                      0-50         通化/tonghua   其它  以实物为准    12%vol   \n",
       "2                      0-50      长城/greatwall    其它  以实物为准       12%   \n",
       "4                    50-100  干露/concha y toro    其它   2016     以实物为准   \n",
       "5  2000-9223372036854775807          拉菲/lafite  波尔多   2015  12.5%vol   \n",
       "8                   100-150          山图/shantu   其它   2016        14   \n",
       "\n",
       "                      葡萄品种   甜度  口感   颜色  ... 品丽珠（Cabernet Franc）  \\\n",
       "1  赤霞珠（Cabernet Sauvignon）  半干型  饱满  宝石红  ...                   0   \n",
       "2  赤霞珠（Cabernet Sauvignon）   干型  柔和  宝石红  ...                   0   \n",
       "4             马尔贝克（Malbec）   干型  饱满  宝石红  ...                   0   \n",
       "5  赤霞珠（Cabernet Sauvignon）   干型  强劲  宝石红  ...                   0   \n",
       "8  西拉/设拉子（Syrah/Shiraz）|其它   干型  柔和  石榴红  ...                   0   \n",
       "\n",
       "  西拉/设拉子（Syrah/Shiraz） 内比奥罗（Nebbiolo）  梅洛（Merlot）  仙粉黛（Zinfandel）  \\\n",
       "1                    0              0           0               0   \n",
       "2                    0              0           0               0   \n",
       "4                    0              0           0               0   \n",
       "5                    0              0           0               0   \n",
       "8                    1              0           0               0   \n",
       "\n",
       "   马尔贝克（Malbec）  长相思（Sauvignon Blanc）  佳美（Gamay）  year  alcohol  \n",
       "1             0                     0          0    -1     12.0  \n",
       "2             0                     0          0    -1     12.0  \n",
       "4             1                     0          0     3     -1.0  \n",
       "5             0                     0          0     4     12.5  \n",
       "8             0                     0          0     3     14.0  \n",
       "\n",
       "[5 rows x 38 columns]"
      ]
     },
     "execution_count": 406,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 521,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 处理价格区间\n",
    "# 价格区间划分为：[0,50],[50,100],[100,150],[150,250],[250,500],[500,1000],[1000,2000],[2000,max]\n",
    "import sys\n",
    "def get_price_scope(price):        #获取价格区间\n",
    "    scope = [[0,50],[50,100],[100,150],[150,250],[250,500],[500,1000],[1000,2000],[2000,sys.maxsize]]\n",
    "    #scope = [[0,50],[50,75],[75,100],[100,150],[150,200],[200,250],[250,300],[300,400],[400,500],[500,1000],[1000,2000],[2000,sys.maxsize]]\n",
    "    for j in range(len(scope)):\n",
    "        if price >= scope[j][0] and price < scope[j][1]:\n",
    "            result = '-'.join(str(x) for x in scope[j])\n",
    "    return result\n",
    "df['price'] = df['price'].map(get_price_scope)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 522,
   "metadata": {},
   "outputs": [],
   "source": [
    "#年份和酒精：\n",
    "\n",
    "dfa = df[(df[\"alcohol\"] !=-1)&(df[\"year\"] !=-1)] #找都不缺的数据\n",
    "dfa.groupby(['price'])['year'].mean()\n",
    "yeard = dict(dfa.groupby('price')['year'].apply(lambda x: x.mode().iloc[0])) #假设价格和年份关系较大\n",
    "_d = df[df['year']==-1]\n",
    "for index, row in _d.iterrows():#年份\n",
    "    for key in yeard.keys():\n",
    "        if row['price']==key:\n",
    "            df.at[index,'year']=yeard.get(key)\n",
    "            break\n",
    "    \n",
    "ad = dict(dfa.groupby('year')['alcohol'].apply(lambda x: x.mode().iloc[0])) #假设酒精和年份关系较大\n",
    "_e = df[df['alcohol']==-1]\n",
    "for index, row in _e.iterrows():#年份\n",
    "    for key in yeard.keys():\n",
    "        if row['year']==key:\n",
    "            df.at[index,'alcohol']=yeard.get(key)\n",
    "            break\n",
    "df.drop([\"name\",\"年份\",\"酒精度\"], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**********************************************************\n",
    "### 步鄹5:保存结果数据\n",
    "本节的清洗数据综上已收尾，将处理结果保存为 wine_processed.csv 供后续模型训练用。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 523,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv(\"wine1_processed.csv\",encoding=\"utf-8_sig\",index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**********************************************************\n",
    "## 五、实验结果\n",
    "本实验完成了全部分清洗数据工作，处理结果保存为 wine_processed.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**********************************************************\n",
    "## 六、实验总结\n",
    "本节对价格区间进行定义，连带前三节的处理结果保存为csv文件，下一节将对本文件进行模型训练。\n",
    "**********************************************************"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
